package com.zz.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.zz.entity.Entry;

public interface EntryRepository extends JpaRepository<Entry,String> {
	//对入库单进行分页
	@Query(value="select en.id,pr.name as productname,en.update_num,en.update_time,s.name as gysname,w.name as ckname,en.notes,en.type,en.po_id,en.entry_price from entry as en left join product pr on (en.product_id=pr.id) left join supplier s on (en.supplier_id=s.id) left join ware w on (en.ware_id=w.id)",
		    countQuery = "SELECT count(*) FROM entry",
			nativeQuery = true)
	public Page<Object[]> showAllEntrylist(Pageable pageable);
	//最近一周
	@Query(value="select en.id,pr.name as productname,en.update_num,en.update_time,s.name as gysname,w.name as ckname,en.notes,en.type,en.po_id,en.entry_price from entry as en left join product pr on (en.product_id=pr.id) left join supplier s on (en.supplier_id=s.id) left join ware w on (en.ware_id=w.id) where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(en.update_time)",
			countQuery = "SELECT count(*) FROM entry where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(update_time)",
			nativeQuery = true)
	public Page<Object[]> showEntrylistBy7day(Pageable pageable);
	//最近一个月
	@Query(value="select en.id,pr.name as productname,en.update_num,en.update_time,s.name as gysname,w.name as ckname,en.notes,en.type,en.po_id,en.entry_price from entry as en left join product pr on (en.product_id=pr.id) left join supplier s on (en.supplier_id=s.id) left join ware w on (en.ware_id=w.id) where date_sub(CURDATE(),INTERVAL 30 DAY) <= DATE(en.update_time)",
			countQuery = "SELECT count(*) FROM entry where date_sub(CURDATE(),INTERVAL 30 DAY) <= DATE(update_time)",
			nativeQuery = true)
	public Page<Object[]> showEntrylistBy30day(Pageable pageable);
	@Query(value="select en.id,pr.name as productname,en.update_num,en.update_time,s.name as gysname,w.name as ckname,en.notes,en.type,en.po_id,en.entry_price from entry as en left join product pr on (en.product_id=pr.id) left join supplier s on (en.supplier_id=s.id) left join ware w on (en.ware_id=w.id) where date_sub(CURDATE(),INTERVAL 30 DAY) <= DATE(en.update_time) and en.product_id=?1",
			countQuery = "SELECT count(*) FROM entry where date_sub(CURDATE(),INTERVAL 30 DAY) <= DATE(update_time) and product_id=?1",
			nativeQuery = true)
	public Page<Object[]> findprofuctIdByPWS30day(String product_id,Pageable pageable);
	//自定义
	@Query(value="select en.id,pr.name as productname,en.update_num,en.update_time,s.name as gysname,w.name as ckname,en.notes,en.type,en.po_id,en.entry_price from entry as en left join product pr on (en.product_id=pr.id) left join supplier s on (en.supplier_id=s.id) left join ware w on (en.ware_id=w.id) where en.update_time between ?1 and ?2",
			countQuery = "SELECT count(*) FROM entry where update_time between ?1 and ?2",
			nativeQuery = true)
	public Page<Object[]> showEntrylistBytime(String starttime,String lasttime,Pageable pageable);
}
